-- 先删除所有依赖表和触发器
drop trigger if exists students_updated_at on students cascade;
drop function if exists update_updated_at() cascade;

-- 按照正确的依赖顺序删除表
drop table if exists student_courses cascade;
drop table if exists student_classes cascade;
drop table if exists courses cascade;
drop table if exists course_versions cascade;
drop table if exists course_series cascade;
drop table if exists teacher_classes cascade;
drop table if exists teachers cascade;
drop table if exists students cascade;
drop table if exists groups cascade;
drop table if exists classes cascade;

-- 先创建班级表
create table classes (
  id varchar(20) primary key,
  name varchar(50) not null,
  description text,
  status varchar(20) default 'active' check (status in ('active', 'inactive')),
  created_at timestamp with time zone default now()
);

-- 修改小组表，添加班级关联
create table groups (
  id varchar(20) primary key,
  class_id varchar(20) references classes(id) on delete cascade,  -- 添加班级外键
  name varchar(50) not null,
  description text,
  status varchar(20) default 'active' check (status in ('active', 'inactive')),
  created_at timestamp with time zone default now()
);

-- 添加索引优化查询性能
create index groups_class_id_idx on groups(class_id);

-- 最后创建学生表（因为它依赖于上面两个表）
create table students (
  id uuid default uuid_generate_v4() primary key,
  account varchar(50) not null unique,
  name varchar(50) not null,
  phone varchar(20),
  school varchar(100),
  class_id varchar(20) references classes(id),
  group_id varchar(20) references groups(id),
  teacher varchar(50),
  points int default 0,
  expire_time timestamp with time zone,
  status varchar(20) default 'inactive',
  gender varchar(10),
  grade varchar(50),
  series varchar(100),
  version varchar(100),
  textbook varchar(100),
  remarks text,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- 添加外键约束索引
create index students_class_id_idx on students(class_id);
create index students_group_id_idx on students(group_id);

-- 创建更新时间触发器
create or replace function update_updated_at()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

create trigger students_updated_at
  before update on students
  for each row
  execute function update_updated_at();

-- 创建系列表
create table course_series (
  id uuid default uuid_generate_v4() primary key,
  name varchar(100) not null,
  created_at timestamp with time zone default now()
);

-- 创建版本表
create table course_versions (
  id uuid default uuid_generate_v4() primary key,
  series_id uuid references course_series(id),
  name varchar(100) not null,
  count int default 0,
  created_at timestamp with time zone default now()
);

-- 创建课程表
create table courses (
  id uuid default uuid_generate_v4() primary key,
  version_id uuid references course_versions(id),
  name varchar(100) not null,
  description text,
  created_at timestamp with time zone default now()
);

-- 创建学生课程分配表(关联表)
create table student_courses (
  id uuid default uuid_generate_v4() primary key,
  student_id uuid references students(id),
  course_id uuid references courses(id),
  status varchar(20) default 'active',
  assigned_at timestamp with time zone default now(),
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now(),
  unique(student_id, course_id)
);

-- 添加索引
create index student_courses_student_id_idx on student_courses(student_id);
create index student_courses_course_id_idx on student_courses(course_id);

-- 创建教师表
create table teachers (
  id uuid default uuid_generate_v4() primary key,
  name varchar(50) not null,
  phone varchar(20),
  email varchar(100),
  subject varchar(50),
  status varchar(20) default 'active' check (status in ('active', 'inactive')),
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- 创建教师-班级关联表
create table teacher_classes (
  id uuid default uuid_generate_v4() primary key,
  teacher_id uuid references teachers(id) on delete cascade,
  class_id varchar(20) references classes(id) on delete cascade,
  created_at timestamp with time zone default now(),
  unique(teacher_id, class_id)
);

-- 创建触发器来更新教师的更新时间
create trigger teachers_updated_at
  before update on teachers
  for each row
  execute function update_updated_at();

-- 为教师表添加RLS策略
alter table teachers enable row level security;

create policy "Enable read access for authenticated users" on teachers
  for select using (auth.role() = 'authenticated');
  
create policy "Enable insert access for authenticated users" on teachers
  for insert with check (auth.role() = 'authenticated');
  
create policy "Enable update access for authenticated users" on teachers
  for update using (auth.role() = 'authenticated');
  
create policy "Enable delete access for authenticated users" on teachers
  for delete using (auth.role() = 'authenticated');

-- 为教师班级关联表添加RLS策略
alter table teacher_classes enable row level security;

create policy "Enable all access for authenticated users" on teacher_classes
  for all using (auth.role() = 'authenticated');

-- 插入一些测试教师数据
insert into teachers (name, phone, email, subject, status) values
  ('李老师', '13900000001', 'li@example.com', '语文', 'active'),
  ('张老师', '13900000002', 'zhang@example.com', '数学', 'active'),
  ('王老师', '13900000003', 'wang@example.com', '英语', 'active');

-- 插入一些测试数据
insert into classes (id, name, status, description) values
  ('1', '高中一年级', 'active', '一年级主班级'),
  ('2', '高中二年级', 'active', '二年级主班级'),
  ('3', '高中三年级', 'active', '三年级主班级');

insert into groups (id, class_id, name) values
  ('A', '1', 'A组'),
  ('B', '1', 'B组'),
  ('C', '2', 'C组');

-- 插入测试学生数据
insert into students (account, name, phone, school, class_id, group_id, teacher, points, expire_time, status) values
  ('student001', '张三', '13800138001', '第一中学', '1', 'A', '李老师', 100, now() + interval '30 days', 'active'),
  ('student002', '李四', '13800138002', '第一中学', '1', 'B', '王老师', 80, now() + interval '60 days', 'active'),
  ('student003', '王五', '13800138003', '第二中学', '2', 'A', '张老师', 90, now() + interval '15 days', 'active'),
  ('student004', '赵六', '13800138004', '第二中学', '2', 'C', '刘老师', 70, now() + interval '45 days', 'inactive');

-- 插入一些测试系列数据
insert into course_series (name) values 
  ('小学英语'),
  ('初中英语'),
  ('高中英语'),
  ('大学英语'),
  ('出国考试'),
  ('成人考试'),
  ('其他课程'),
  ('常用英语'),
  ('行业英语'),
  ('学前英语');

-- 为每个系列插入一些版本数据
insert into course_versions (series_id, name, count) 
select 
  s.id,
  '牛津上海版' as name,
  1992 as count
from course_series s
where s.name = '小学英语';

-- 为每个系列插入多个版本数据
insert into course_versions (series_id, name, count) 
select 
  s.id,
  versions.name,
  versions.count
from course_series s
cross join (
  values 
    ('牛津上海版', 1992),
    ('牛津译林预备版', 494),
    ('北师大版(2013)', 2228),
    ('鲁教版（2012）', 2772),
    ('北师大版', 2191),
    ('新世纪版初中英语', 1723),
    ('牛津英语（A版）', 1909),
    ('EEC英语(2012)', 1996),
    ('冀教版(2012)', 1713)
) as versions(name, count)
where s.name in ('小学英语', '初中英语', '高中英语');

-- 为每个版本插入一些测试课程
insert into courses (version_id, name, description)
select
  v.id,
  'Unit ' || n || ' - ' || case 
    when n = 1 then 'Introduction'
    when n = 2 then 'Grammar'
    when n = 3 then 'Vocabulary'
    when n = 4 then 'Reading'
    when n = 5 then 'Writing'
    else 'Practice'
  end,
  'Course description for Unit ' || n
from course_versions v
cross join generate_series(1, 6) n;

-- 创建RLS策略
alter table students enable row level security;

create policy "Public students are viewable by everyone."
  on students for select
  using ( true );

create policy "Users can insert their own students."
  on students for insert
  with check ( auth.role() = 'authenticated' );

create policy "Users can update their own students."
  on students for update
  using ( auth.role() = 'authenticated' );

create policy "Users can delete their own students."
  on students for delete
  using ( auth.role() = 'authenticated' );

-- 添加RLS策略
alter table student_courses enable row level security;

create policy "Public student_courses are viewable by everyone."
  on student_courses for select
  using ( true );

create policy "Users can insert student_courses."
  on student_courses for insert
  with check ( auth.role() = 'authenticated' );

create policy "Users can update student_courses."
  on student_courses for update
  using ( auth.role() = 'authenticated' );

create policy "Users can delete student_courses."
  on student_courses for delete
  using ( auth.role() = 'authenticated' );
